﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace CMDBImportCIDLL
{
    public class ExcelData
    {
        public string SheetName { get; set; }
        public DataTable DataTable { get; set; }
    }

    public class CIName
    {
        public string CiName { get; set; }
        public string SheetName { get; set; }
    }

    public class CommonExcelOperate
    {
        public List<ExcelData> GetExcelData(string filePath)
        {
            List<ExcelData> result = new List<ExcelData>();
            string ext = System.IO.Path.GetExtension(filePath);
            string strConn = string.Empty;
            switch (ext)
            {
                case ".xlsx":
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=NO;IMEX=1\"";
                    break;
                default:
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=NO;IMEX=1\"";
                    break;
            }
            using (OleDbConnection myConn = new OleDbConnection(strConn))
            {
                myConn.Open();
                DataTable sheetNames = myConn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                foreach (DataRow dr in sheetNames.Rows)
                {
                    ExcelData excelData = new ExcelData();
                    string sheetName = dr[2].ToString();
                    excelData.SheetName = sheetName.Trim('\'').TrimEnd('$');
                    string strCom = " SELECT * FROM [" + sheetName + "]";
                    OleDbDataAdapter myCommand = new OleDbDataAdapter(strCom, myConn);
                    DataSet ds = new DataSet();
                    myCommand.Fill(ds);
                    DataTable dt = ds.Tables[0];
                    for (int i = dt.Rows.Count - 1; i >= 0; i--)
                    {
                        DataRow item = dt.Rows[i];
                        if (item[0] == null || string.IsNullOrEmpty(item[0].ToString().Trim()))
                        {
                            dt.Rows.Remove(item);
                        }
                        else
                        {
                            for (int j = 0; j < dt.Columns.Count; j++)
                            {
                                dt.Rows[i][j] = dt.Rows[i][j].ToString().Trim();
                            }
                        }
                    }
                    excelData.DataTable = dt;
                    result.Add(excelData);
                }
                myConn.Close();
            }

            return result;
        }

        public bool CheckExcelColumn(List<ExcelData> dataList, List<Sheet> sheetList, out string resultStr)
        {
            string ret = string.Empty;
            for (int i = 0; i < sheetList.Count(); i++)
            {
                ExcelData ed = dataList.Find(delegate(ExcelData p) { return p.SheetName == sheetList[i].SheetName; });
                if (ed != null)
                {
                    DataTable dt = ed.DataTable;
                    for (int j = 0; j < sheetList[i].ColumnList.Count; j++)
                    {
                        if (!dt.Rows[0][j].ToString().ToLower().Trim().Replace("\n", "").Contains(sheetList[i].ColumnList[j].ColumnName.ToString().ToLower().Trim()))
                        {
                            ret = ret + Environment.NewLine + string.Format("Excel中 ${0}$ Sheet页的第{1}列（{2} != {3}）不符合模板,请检查!", sheetList[i].SheetName, j, sheetList[i].ColumnList[j].ColumnName.ToString(), dt.Rows[0][j].ToString());
                        }
                    }
                }
                else
                {
                    ret = ret + Environment.NewLine + string.Format("Excel中 ${0}$ Sheet页不存在,请检查!", sheetList[i].SheetName);
                }
            }
            resultStr = ret;
            return !string.IsNullOrEmpty(resultStr);
        }

        public bool CheckCiNameRepeat(List<ExcelData> dataList, List<Sheet> sheetList, out string resultStr)
        {
            resultStr = string.Empty;
            List<CIName> namestrlist = new List<CIName>();
            for (int i = 0; i < sheetList.Count(); i++)
            {
                if (sheetList[i].SheetName != "关系表")
                {
                    DataTable dt = dataList.Find(delegate(ExcelData p) { return p.SheetName == sheetList[i].SheetName; }).DataTable;
                    for (int j = 1; j < dt.Rows.Count; j++)
                    {
                        CIName ciname = new CIName();
                        ciname.CiName = dt.Rows[j][0].ToString();
                        ciname.SheetName = sheetList[i].SheetName;
                        namestrlist.Add(ciname);
                    }
                }
            }
            for (int i = 0; i < namestrlist.Count; i++)
            {
                for (int j = i + 1; j < namestrlist.Count; j++)
                {
                    if (namestrlist[i].CiName.ToLower().Equals(namestrlist[j].CiName.ToLower()))
                    {
                        resultStr = resultStr + Environment.NewLine + string.Format("Excel中 ${0}$ Sheet页的CIName: {1} 与 ${2}$ Sheet页的CI重复", namestrlist[i].SheetName, namestrlist[i].CiName, namestrlist[j].SheetName);
                    }
                }
            }

            return !string.IsNullOrEmpty(resultStr);
        }

        public bool CheckRelationshipCiNameNotExist(List<ExcelData> dataList, List<Sheet> sheetList, out string resultStr)
        {
            resultStr = string.Empty;
            List<string> namestrlist = new List<string>();
            for (int i = 0; i < sheetList.Count(); i++)
            {
                if (sheetList[i].SheetName != "关系表")
                {
                    DataTable dt = dataList.Find(delegate(ExcelData p) { return p.SheetName == sheetList[i].SheetName; }).DataTable;
                    for (int j = 1; j < dt.Rows.Count; j++)
                    {
                        namestrlist.Add(dt.Rows[j][0].ToString());
                    }
                }
            }
            DataTable shipdt = dataList.Find(delegate(ExcelData p) { return p.SheetName == "关系表"; }).DataTable;
            for (int j = 1; j < shipdt.Rows.Count; j++)
            {
                if (!namestrlist.Contains(shipdt.Rows[j][0].ToString()))
                {
                    resultStr = resultStr + Environment.NewLine + string.Format("Excel中 $关系表$ Sheet页的CIName: {0} 不存在", shipdt.Rows[j][0].ToString());
                }
                if (!namestrlist.Contains(shipdt.Rows[j][2].ToString()))
                {
                    resultStr = resultStr + Environment.NewLine + string.Format("Excel中 $关系表$ Sheet页的CIName: {0} 不存在", shipdt.Rows[j][2].ToString());
                }
            }

            return !string.IsNullOrEmpty(resultStr);
        }
    }
}
